package msu.ml.data;

import msu.ml.core.*;
import msu.ml.data.level2.*;
import java.sql.*;
import java.io.*;
import java.util.regex.*;
import java.util.*;
import weka.core.*;

/**
 * The IDataLoader interface is used to load/parse an instances object
 * from a file. By using the isSupportedData(String) method, a set of
 * IDataLoader objects can be examined, looking for one with the ability
 * to load the file you are asking for.
 *
 * @author Reginald M Mead
 * @version 1.0
 */
public class DatabaseModel 
{
   private Connection conn;
   private String localDataDir;

   public DatabaseModel(String url, String user, String pass)
   {
      try
      {
         Class.forName("com.mysql.jdbc.Driver").newInstance();
         conn = DriverManager.getConnection(url, user, pass);
      }
      catch(Exception e)
      {
         System.err.println("Error Opening Database: " + e.getMessage());
      }

   }

   //
   // Scan Methods
   //
   public String [] selectScan(int id)
   {
      return selectQuery("SELECT * FROM scans WHERE scan_id="+id+";");
   }

   public void removeScan(int id)
   {
      updateQuery("DELETE FROM scans WHERE scan_id=" + id + ";", false);
   }

   public int insertScan(int sweepId, Scan scan, int scanIndex)
   {
      Ray ray = scan.getRay(0);

      Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
      calendar.setTime(ray.getTime());

      String query = "INSERT INTO scans VALUES (NULL, " +
         sweepId + ", " +
         "(SELECT vcp_scan_id FROM vcp_scans WHERE " +
         "pattern_id=" + ray.getVolumeCoveragePattern() +
         " AND vcp_scans.index=" + (scanIndex + 1) + "), " +
         String.format("'%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$td', ", calendar) +
         String.format("%1$d, ", (int)ray.getUnambiguousRange()) +
         String.format("%1$3.1f, ", ray.getElevation()) + ");";

      return updateQuery(query, true);
   }

   //
   // Sweep Methods
   //
   public String [] selectSweep(int id)
   {
      return selectQuery("SELECT * FROM sweeps WHERE sweep_id="+id+";");
   }

   public void removeSweep(int id, boolean cascade)
   {
      if(cascade)
      {
         updateQuery("DELETE FROM pulse_volumes p WHERE p.sweep_id="+id+";", false);
         updateQuery("DELETE FROM scans s WHERE s.sweep_id="+id+";", false);
      }
      updateQuery("DELETE FROM sweeps WHERE sweep_id=" + id + ";", false);
   }

   public int insertSweep(int volumeId, String classification)
   {
      String query = "INSERT INTO sweeps VALUES (NULL, " +
         volumeId + ", 0.5, " + classification + ");";
      return updateQuery(query, true);
   }

   public int insertSweep(int volumeId, int classification)
   {
      String query = "INSERT INTO sweeps VALUES (NULL, " +
         volumeId + ", 0.5, " + classification + ");";
      return updateQuery(query, true);
   }

   //
   // Volume Methods
   //
   public String [] selectVolume(int id)
   {
      return selectQuery("SELECT * FROM volumes WHERE volume_id="+id+";");
   }

   public void removeVolume(String target, boolean cascade)
   {
      removeVolume(lookupVolumeId(target), cascade);
   }

   public void removeVolume(int id, boolean cascade)
   {
      if(cascade)
      {
         //Delete Pulse Volumes
         updateQuery("DELETE pulse_volumes FROM pulse_volumes, sweeps" + 
               " WHERE pulse_volumes.sweep_id = sweeps.sweep_id AND " + 
               "sweeps.volume_id = " + id + ";", false);
         //Delete Scans
         updateQuery("DELETE scans FROM scans, sweeps" + 
               " WHERE scans.sweep_id = sweeps.sweep_id AND " + 
               "sweeps.volume_id = " + id + ";", false);
         //Delete Sweeps
         updateQuery("DELETE FROM sweeps WHERE volume_id=" + id + ";", false);
      }
      //Delete Volumes
      updateQuery("DELETE FROM volumes WHERE volume_id=" + id + ";", false);
   }

   public int insertVolume(String target)
   {
      Pattern p = Pattern.compile( "^6500(\\w\\w\\w\\w)" + 
            "(\\d\\d\\d\\d)(\\d\\d)(\\d\\d)" +
            "_(\\d\\d)(\\d\\d)(\\d\\d).*");

      Matcher m = p.matcher(target);

      if(m.matches())
      {
         String query = "INSERT INTO volumes VALUES (NULL, '" +
            target + "', " + // filename
            String.format("'%1$s-%2$s-%3$s', ", m.group(2), 
                  m.group(3), m.group(4)) + //Year-Month-Day
            String.format("'%1$s:%2$s:%3$s', ", m.group(5), 
                  m.group(6), m.group(7)) + //Hour:Minute:Second
            "(SELECT site_id FROM sites WHERE call_sign='"
            + m.group(1) + "'));"; //call 

         return updateQuery(query, true);
      }

      p = Pattern.compile( "^(\\w\\w\\w\\w)" + 
            "(\\d\\d\\d\\d)(\\d\\d)(\\d\\d)" +
            "_(\\d\\d)(\\d\\d)(\\d\\d).*");

      m = p.matcher(target);

      if(m.matches())
      {
         String query = "INSERT INTO volumes VALUES (NULL, '" +
            target + "', " + // filename
            String.format("'%1$s-%2$s-%3$s', ", m.group(2), 
                  m.group(3), m.group(4)) + //Year-Month-Day
            String.format("'%1$s:%2$s:%3$s', ", m.group(5), 
                  m.group(6), m.group(7)) + //Hour:Minute:Second
            "(SELECT site_id FROM sites WHERE call_sign='"
            + m.group(1) + "'));"; //call 

         return updateQuery(query, true);
      }

      return -1;
   }

   //
   // Pulse Volume Methods
   //
   public int lookupVolumeId(String name)
   {
      String query = "SELECT volume_id FROM volumes " +
         "WHERE ncdc_filename='"+name+"';";
      String [] result = selectQuery(query);
      if(result != null && result.length > 0)
         return Integer.parseInt(result[0]);
      return -1;
   }

   public String [] selectPulseVolume(int id)
   {
      return selectQuery("SELECT * FROM pulse_volumes WHERE pulse_id="+id+";");
   }

   public void removePulseVolume(int id)
   {
      updateQuery("DELETE FROM pulse_volumes WHERE pulse_id=" + id + ";", false);
   }

   public int insertPulseVolume(int id, boolean combined, Instance instance)
   {
      String sid = "" + id;
      String columns = "";
      String values = "";

      for(int i = 0; i < instance.numAttributes(); i++)
      {
         columns += String.format(", `%1$s`", instance.attribute(i).name());
         values += String.format(", %1$5.2f", instance.value(i));
      }

      String query = "INSERT INTO pulse_volumes (`pulse_id`, `sweep_id`, `scan_id`"
         + columns + ") VALUES (NULL, " + (combined ? sid : "NULL") 
         + ", " + (combined ? "NULL" : sid) + values + ");";

      return updateQuery(query, true);
   }

   public Instances getNonBiologicalInstances()
   {
      return getInstancesForClass(0, "nonbio");
   }

   public Instances getBiologicalInstances()
   {
      return getInstancesForClass(1, "bio");
   }

   public Instances getInstancesForClass(int cl, String name)
   {
      Instances result = null;
      Statement s = null;

      try
      {
         String query = String.format("SELECT azimuth, range, reflectivity, radial_velocity, spectrum_width, class FROM pulse_volumes WHERE class = %1$d", cl);

         s = conn.createStatement();
         ResultSet rs = s.executeQuery(query);
         ResultSetMetaData meta = rs.getMetaData();
         int columns = meta.getColumnCount();

         FastVector v = new FastVector(columns);
         for(int i = 0; i < columns - 1; i++)
            v.addElement(new Attribute(meta.getColumnName(i + 1)));

         FastVector classes = new FastVector(2);
         classes.addElement("0");
         classes.addElement("1");
         v.addElement(new Attribute("class", classes));
         
         result = new Instances(name, v, 1000);

         while(rs.next())
         {
            double [] features = new double[columns];
            for(int i = 0; i < columns; i++)
               features[i] = rs.getDouble(i + 1);

            result.add(new Instance(1.0, features));
         }
      }
      catch(Exception e) { System.out.println(e.getMessage()); }
      finally
      {
         if(s != null)
            try {s.close();} catch(java.sql.SQLException e){}
      }
      return result;
   }

   public Instances getInstances(String volumeName)
   {
      Instances result = null;
      Statement s = null;

      try
      {
         String query = String.format("SELECT azimuth, range, reflectivity, radial_velocity, spectrum_width, ref_variance, ref_kurtosis, ref_skewness, vel_variance, vel_kurtosis, vel_skewness, spec_variance, spec_kurtosis, spec_skewness, pulse_volumes.class FROM (pulse_volumes JOIN (sweeps as s JOIN volumes as v ON s.volume_id = v.volume_id) ON pulse_volumes.sweep_id = s.sweep_id) WHERE ncdc_filename = \"%1$s\";", volumeName);

         s = conn.createStatement();
         ResultSet rs = s.executeQuery(query);
         ResultSetMetaData meta = rs.getMetaData();
         int columns = meta.getColumnCount();

         FastVector v = new FastVector(columns);
         for(int i = 0; i < columns - 1; i++)
            v.addElement(new Attribute(meta.getColumnName(i + 1)));

         FastVector classes = new FastVector(2);
         classes.addElement("0");
         classes.addElement("1");
         v.addElement(new Attribute("class", classes));
         
         result = new Instances(volumeName, v, 1000);

         while(rs.next())
         {
            double [] features = new double[columns];
            for(int i = 0; i < columns; i++)
               features[i] = rs.getDouble(i + 1);

            result.add(new Instance(1.0, features));
         }
      }
      catch(Exception e) { System.out.println(e.getMessage()); }
      finally
      {
         if(s != null)
            try {s.close();} catch(java.sql.SQLException e){}
      }

      result.setClassIndex(result.numAttributes() - 1);
      return result;

   }

   public ArrayList<IPulseVolume> getPulseVolumes(String ncdcName, 
         IPulseVolumeSQLAdapter adapter)
   {
      Statement s = null;
      ArrayList<IPulseVolume> pulses = new ArrayList<IPulseVolume>();

      try
      {
         String query = adapter.createQuery(ncdcName);
         s = conn.createStatement();
         ResultSet rs = s.executeQuery(query);
         while(rs.next())
         {
            pulses.add(adapter.getPulseVolume(rs));
         }
         return pulses;
      }
      catch(Exception e) { System.out.println(e.getMessage()); }
      finally
      {
         if(s != null)
         {
            try {s.close();}
            catch(java.sql.SQLException e){}
         }
      }
      return null;

   }

   public int pulseVolumeCount(String volumeName)
   {
      String [] queryResult = selectQuery(
            "SELECT COUNT(pulse_volumes.pulse_id) FROM volumes, " + 
            "sweeps, pulse_volumes " + 
            "WHERE pulse_volumes.sweep_id = sweeps.sweep_id AND " +
            "sweeps.volume_id = volumes.volume_id AND " + 
            "volumes.ncdc_filename = '" + volumeName + "' ");

      try
      {
         return Integer.parseInt(queryResult[0]);
      }
      catch(Exception e)
      {
         System.out.println(e.getMessage());
      }
      return -1;
   }

   public List<String> getVolumeNames()
   {
      return getVolumeNames(0, false);
   }

   public List<String> getVolumeNames(int limit, boolean rand)
   {
      String query = "SELECT ncdc_filename FROM volumes ";

      if(rand)
         query += "ORDER BY RAND() ";

      if(limit > 0)
         query += "LIMIT " + limit;

      return selectMultiQuery(query);
   }

   public HashMap<String, Integer> getClassesForVolumes(List<String> volumes)
   {
      HashMap<String, Integer> classes = new HashMap<String, Integer>();

      if(volumes.size() == 0)
         return classes;

      Iterator<String> iter = volumes.iterator();
      
      StringBuilder builder = new StringBuilder("SELECT ncdc_filename, class FROM (volumes as v JOIN sweeps as s ON v.volume_id = s.volume_id) WHERE ncdc_filename IN (");
      builder.append("\"").append(iter.next()).append("\"");
      while(iter.hasNext())
      {
         builder.append(",").append("\"").append(iter.next()).append("\"");
      }
      builder.append(");");
      
      String query = builder.toString();

      Statement s = null;
      try
      {
         ArrayList<String> result = new ArrayList<String>();
         s = conn.createStatement();
         ResultSet rs = s.executeQuery(query);
         while(rs.next())
            classes.put(rs.getString(1), new Integer(rs.getInt(2)));
         return classes;
      }
      catch(Exception e) { System.out.println(e.getMessage()); }
      finally
      {
         if(s != null)
         {
            try {s.close();}
            catch(java.sql.SQLException e){}
         }
      }
      return classes;
   }

   protected List<String> selectMultiQuery(String query)
   {
      Statement s = null;

      try
      {
         ArrayList<String> result = new ArrayList<String>();
         s = conn.createStatement();
         ResultSet rs = s.executeQuery(query);
         while(rs.next())
         {
            ResultSetMetaData meta = rs.getMetaData();
            int count = meta.getColumnCount();
            String nextResult = "";
            for(int i = 0; i < count; i++)
               nextResult += rs.getString(i + 1);
            result.add(nextResult);
         }
         return result;
      }
      catch(Exception e) { System.out.println(e.getMessage()); }
      finally
      {
         if(s != null)
         {
            try {s.close();}
            catch(java.sql.SQLException e){}
         }
      }
      return null;
   }

   //
   // Helper Methods
   //
   protected String [] selectQuery(String query)
   {
      Statement s = null;

      try
      {
         s = conn.createStatement();
         ResultSet rs = s.executeQuery(query);
         while(rs.next())
         {
            ResultSetMetaData meta = rs.getMetaData();
            int count = meta.getColumnCount();
            String [] result = new String[count];
            for(int i = 1; i <= count; i++)
               result[i-1] = rs.getString(i);
            return result;
         }
      }
      catch(Exception e) { System.out.println(e.getMessage()); }
      finally
      {
         if(s != null)
         {
            try {s.close();}
            catch(java.sql.SQLException e){}
         }
      }
      return null;
   }

   public int updateQuery(String query, boolean keys)
   {
      Statement s = null;
      try
      {
         s = conn.createStatement();
         if(keys)
         {
            s.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            ResultSet rs = s.getGeneratedKeys();
            if(rs.next()) return rs.getInt(1);
         }
         else
         {
            s.executeUpdate(query);
            return 0;
         }
      }
      catch(Exception e) { System.out.println(e.getMessage()); }
      finally
      {
         if(s != null)
         {
            try {s.close();}
            catch(java.sql.SQLException e){}
         }
      }
      return -1;
   }

   protected void finalize() throws Throwable
   {
      try
      {
         if(conn != null)
            conn.close();
      }
      finally
      {
         super.finalize();
      }
   } 
}
